//author:王凯平

import com.xxx.bean.course;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Demo {
    public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    //8.0以上版本的数据库，驱动器是com.mysql.cj.jdbc.Driver，而不是之前的com.mysql.jdbc.Driver
    public static final String DB_URL = "jdbc:mysql://localhost:3306/javaee?serverTimezone" +
            "=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false";
    //8.0以上版本的数据库要在后面加上  ?useSSL=false&serverTimezone=UTC
    //在这个语句中，localhost是数据库链接名称，3306是端口号，jdbc是要访问的数据库
    public static final String USER = "root";//数据库用户名
    public static final String PASS = "123456";//数据库密码
    public static void main(String[] args) {
        Sheet sheet;
        Workbook workbook;
        Cell cell;
        String teacher,content;
        String[] contents;
        ResultSet rs;
        String pattern="(.*?)\n周数:(.*?)校区: (.*?)地点: (.*?)教学班组成: (.*?) 选课人数：\\d* 选课备注：";
        Pattern r = Pattern.compile(pattern);
        List<course> courses = new ArrayList<>();
        try{
            workbook = Workbook.getWorkbook(new File("src/main/resources/test.xls"));
            sheet=workbook.getSheet(0);
            int clos=sheet.getColumns();//得到所有的列
            int rows=sheet.getRows();//得到所有的行
            for (int i=2;i<rows;i++){
                for (int j=1;j<clos;j++){
                    cell=sheet.getCell(j,i);
                    teacher=sheet.getCell(0,i).getContents();
                    if ("".equals(cell.getContents())){continue;}
                    content=cell.getContents();
                    contents=content.split("无");
                    for (String s : contents) {
//                        System.out.println(contents[k]+"\n");
                        Matcher m = r.matcher(s);
                        if (m.find()) {
                            course c = new course();
//                            System.out.println(m.group(0));     //当前课程全部内容
//                            System.out.println(m.group(1));     //课程名
                            c.setC_name(m.group(1));
//                            System.out.println(m.group(2));     //课程周次
//                            System.out.println(m.group(3));     //课程校区
                            c.setC_address(m.group(3) + m.group(4));
//                            System.out.println(m.group(4));     //所在教室
//                            System.out.println(m.group(5));     //授课班级
                            c.setC_class(m.group(5));
                            c.setT_name(teacher);
                            System.out.println(c);
                            courses.add(c);
                        }
                    }
                }
                System.out.println("\n");
            }
            workbook.close();
//            操作数据库
            Connection conn;
            //注册jdbc驱动
            Class.forName(JDBC_DRIVER);
            //打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("连接成功");
            String sql="INSERT INTO computer_course(course_id,c_name,t_name,c_class,c_address) VALUES(?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i=0;i<courses.size();i++){
                ps.setInt(1,0);
                ps.setString(2,courses.get(i).getC_name());
                ps.setString(3,courses.get(i).getT_name());
                ps.setString(4,courses.get(i).getC_class());
                ps.setString(5,courses.get(i).getC_address());
                ps.executeUpdate();
                if (i==0){
                    System.out.println("添加中...");
                }
            }
            List<course> courses_list = new ArrayList<>();
            String sql_find = "Select * FROM computer_course";
            PreparedStatement pst=conn.prepareStatement(sql_find);
            rs = pst.executeQuery();
            while(rs.next()){
                course c = new course();
                c.setCourse_id(rs.getInt("course_id"));
                c.setC_name(rs.getString("c_name"));
                c.setT_name(rs.getString("t_name"));
                c.setC_class(rs.getString("c_class"));
                c.setC_address(rs.getString("c_address"));
                courses_list.add(c);
            }

            conn.close();
            ps.close();

//            导出excel文件
            WritableWorkbook wb;
            String fileName = "src/main/resources/数据导出.xls";
            File file=new File(fileName);
            if (!file.exists()) {
                file.createNewFile();
            }
            wb = Workbook.createWorkbook(file);
            WritableSheet ws = wb.createSheet("统计",0);
            Label label_tname = new Label(0,0,"教师姓名");
            Label label_id = new Label(1,0,"课程编号");
            Label label_cname = new Label(2,0,"课程名称");
            Label label_cclass = new Label(3,0,"授课班级");
            Label label_caddress = new Label(4,0,"教室名称");
            ws.addCell(label_tname);
            ws.addCell(label_id);
            ws.addCell(label_cname);
            ws.addCell(label_cclass);
            ws.addCell(label_caddress);
            for (int i=0;i<courses_list.size();i++){
                Label label1 =new Label(0,i+1,courses_list.get(i).getT_name());
                Label label2 =new Label(1,i+1,courses_list.get(i).getCourse_id()+"");
                Label label3 =new Label(2,i+1,courses_list.get(i).getC_name());
                Label label4 =new Label(3,i+1,courses_list.get(i).getC_class());
                Label label5 =new Label(4,i+1,courses_list.get(i).getC_address());
                ws.addCell(label1);
                ws.addCell(label2);
                ws.addCell(label3);
                ws.addCell(label4);
                ws.addCell(label5);
            }
            wb.write();
            wb.close();
        }catch (SQLException sqlException){
            System.out.println("ERROR"+sqlException);
        } catch (Exception e){
            System.out.println("ERROR"+e);
        }
    }
}
